# LuckPay Web Manage System
#
# Copyright (c) 2016 Lucky Byte, Inc.
#
express = require 'express'
pgsql   = require 'pg'
uuid    = require 'uuid'
moment  = require 'moment'
router = express.Router()
module.exports = router

router.get '/', (req, res, next) ->
    await pgsql.connect settings.pgsql_url, defer err, client, done
    if err then done(client); return next(err)

    where_clause = "(
        name like $1 or contact like $1 or phone like $1 or
        address like $1 or notes like $1
    )"
    # 查询记录总数
    await client.query "select count(*) as count from pay_p2_fund
        where #{ where_clause }", [
            '%' + res.locals.keyword + '%'
        ], defer err, result
    if err then done(client); return next(err)
    res.locals.total = result.rows[0].count

    # 查询当前页面中显示的记录
    await client.query "select * from pay_p2_fund where #{ where_clause }
        order by serial offset $2::int limit $3::int", [
            '%' + res.locals.keyword + '%',
            res.locals.offset,
            res.locals.page_rows
        ], defer err, result
    if err then done(client); return next(err)
    done()
    res.render 'p2/fund/index', records : result.rows


router.get '/new', (req, res, next) ->
    res.locals.breadcrumbs.push 'title': '新增资金池'
    res.render 'p2/fund/new', record: {}


router.post '/new', (req, res, next) ->
    for k in ['name', 'overdraw', 'cordon', 'agent', 'contact', 'phone']
        return next(new Error("请求缺少参数[#{k}]")) if not req.body[k]

    await pgsql.connect settings.pgsql_url, defer err, client, done
    if err then done(client); return next(err)

    await client.query \
        "insert into pay_p2_fund (
            uuid, name, overdraw, cordon, agent, contact, phone,
            address, email, notes
        ) values (
            $1, $2, $3, $4, $5, $6, $7, $8, $9, $10
        )", [
            uuid.v4(),
            req.body.name,
            req.body.overdraw,
            req.body.cordon,
            req.body.agent,
            req.body.contact,
            req.body.phone,
            req.body.address,
            req.body.email,
            req.body.notes
        ], defer err, result
    if err then done(client); return next(err)
    done()
    res.redirect '/p2/fund'


router.get '/edit/:id', (req, res, next) ->
    res.locals.breadcrumbs.push 'title': '修改资金池'

    await pgsql.connect settings.pgsql_url, defer err, client, done
    if err then done(client); return next(err)

    await client.query \
        "select * from pay_p2_fund
            where serial = $1::int or uuid = $2", [
            parseInt(req.params.id) or 0, req.params.id
        ], defer err, result
    if err then done(client); return next(err)
    done()

    if result.rows.length != 1
        return next(new Error("查无此记录[#{req.params.id}]"))
    res.render 'p2/fund/edit', record: result.rows[0]


router.post '/edit', (req, res, next) ->
    for k in ['serial', 'uuid', 'overdraw', 'cordon',
        'name', 'agent', 'contact', 'phone']
        return next(new Error("请求缺少参数[#{k}]")) if not req.body[k]

    await pgsql.connect settings.pgsql_url, defer err, client, done
    if err then done(client); return next(err)

    await client.query \
        "update pay_p2_fund set
            name = $1, overdraw = $2, cordon = $3,
            agent = $4, contact = $5, phone = $6,
            address = $7, email = $8, notes = $9
            where serial = $10 and uuid = $11", [
            req.body.name,
            req.body.overdraw,
            req.body.cordon,
            req.body.agent,
            req.body.contact
            req.body.phone,
            req.body.address,
            req.body.email,
            req.body.notes,
            req.body.serial,
            req.body.uuid
        ], defer err, result
    if err then done(client); return next(err)
    done()
    res.redirect '/p2/fund'


router.post '/delete', (req, res, next) ->
    res.type 'json'
    for k in ['serial', 'uuid']
        return next(new Error("请求缺少参数[#{k}]")) if not req.body[k]

    await pgsql.connect settings.pgsql_url, defer err, client, done
    if err then done(client); return next(err);

    # 需要删除所有关联的资金变动历史
    await client.query \
        "delete from pay_p2_fund_alter where p2_fund = $1", [
            req.body.uuid,
        ], defer err, result
    if err then done(client); return next(err);

    await client.query \
        "delete from pay_p2_fund where serial = $1 and uuid = $2", [
            req.body.serial, req.body.uuid,
        ], defer err, result
    if err then done(client); return next(err);
    done()
    res.json succ: true, redirect: '/p2/fund'


router.get '/recharge/:id', (req, res, next) ->
    res.locals.breadcrumbs.push 'title': '资金池充值'

    await pgsql.connect settings.pgsql_url, defer err, client, done
    if err then done(client); return next(err)

    await client.query \
        "select * from pay_p2_fund
            where serial = $1::int or uuid = $2", [
            parseInt(req.params.id) or 0, req.params.id
        ], defer err, result
    if err then done(client); return next(err)
    done()

    if result.rows.length != 1
        return next(new Error("查无此记录[#{req.params.id}]"))
    res.render 'p2/fund/recharge', record: result.rows[0]


router.post '/recharge', (req, res, next) ->
    for k in ['serial', 'uuid', 'recharge']
        return next(new Error("请求缺少参数[#{k}]")) if not req.body[k]

    await pgsql.connect settings.pgsql_url, defer err, client, done
    if err then done(client); return next(err)

    recharge = parseFloat(req.body.recharge)
    if not recharge
        return next new Error("充值金额 #{req.body.recharge} 无效")

    await client.query \
        "select fund_total, fund_remains, fund_history
            from pay_p2_fund
            where serial = $1::int or uuid = $2", [
            parseInt(req.body.serial) or 0, req.body.uuid
        ], defer err, result
    if err then done(client); return next(err)
    if result.rows.length != 1
        done()
        return next new Error('未查询到记录')

    # 记录充值历史
    fund_history = result.rows[0].fund_history or []
    fund_history.push
        time: moment().format('YYYY-MM-DD HH:mm:ss')
        user: req.session.userinfo.realname
        amt: recharge
        total: result.rows[0].fund_total
        remains: result.rows[0].fund_remains

    # 记录资金变动
    await client.query \
        "insert into pay_p2_fund_alter (
            p2_fund, type, remains, amt, notes
        ) values (
            $1, 1, $2, $3, $4
        )", [
            req.body.uuid, result.rows[0].fund_remains, 0 - recharge
            "用户 '#{req.session.userinfo.realname}' 充值 #{recharge} 元"
        ], defer err, result
    if err then done(client); return next(err)

    await client.query \
        "update pay_p2_fund set
            fund_total = fund_total + $1,
            fund_remains = fund_remains + $1,
            fund_history = $2
            where serial = $3::int or uuid = $4", [
            recharge, JSON.stringify(fund_history),
            parseInt(req.body.serial) or 0, req.body.uuid
        ], defer err, result
    if err then done(client); return next(err)
    done()
    res.redirect '/p2/fund'


router.all '/recharge/history/:id', (req, res, next) ->
    res.locals.breadcrumbs.push 'title': '充值历史记录'

    where_clause = "(
        h->>'user' like $1 or h->>'amt' like $1 or h->>'time' like $1
    )"
    # 如果没有提供日期，则仅查询当日
    res.locals.start_date = req.body.start_date or \
        req.query.start_date or moment().format('YYYY-MM-DD')
    res.locals.end_date = req.body.end_date or \
        req.query.end_date or moment().format('YYYY-MM-DD')

    where_clause += """
        and cast(h->>'time' as date) >= '#{res.locals.start_date}'::date
        and cast(h->>'time' as date) <= '#{res.locals.end_date}'::date
    """

    if req.body.min_amt and req.body.min_amt.length > 0
        min_amt = parseFloat(req.body.min_amt) or 0
        where_clause += " and cast(h->>'amt' as decimal) >= #{min_amt}"
        res.locals.min_amt = min_amt.toString()
    if req.body.max_amt and req.body.max_amt.length > 0
        max_amt = parseFloat(req.body.max_amt) or 0
        where_clause += " and cast(h->>'amt' as decimal) <= #{max_amt}"
        res.locals.max_amt = max_amt.toString()

    await pgsql.connect settings.pgsql_url, defer err, client, done
    if err then done(client); return next(err)

    await client.query \
        "select count(h)::int as count
            from pay_p2_fund as f,
                json_array_elements(f.fund_history) as h
            where #{where_clause} and
                (serial = $2::int or uuid = $3)", [
            "%#{res.locals.keyword}%"
            parseInt(req.params.id) or 0, req.params.id
        ], defer err, result
    if err then done(client); return next(err)
    res.locals.total = result.rows[0].count

    await client.query \
        "select f.name as name, f.agent as agent, f.contact as contact,
            h->>'time' as time, h->>'user' as user, h->>'amt' as amt,
            h->>'total' as total, h->>'remains' as remains
            from pay_p2_fund as f,
                json_array_elements(f.fund_history) as h
            where #{where_clause} and
                (serial = $2::int or uuid = $3)
            order by h->>'time' desc offset $4::int limit $5::int", [
            "%#{res.locals.keyword}%"
            parseInt(req.params.id) or 0, req.params.id
            res.locals.offset, res.locals.page_rows
        ], defer err, result
    if err then done(client); return next(err)
    done()
    res.render 'p2/fund/history', records: result.rows


router.all '/alter/:id', (req, res, next) ->
    res.locals.breadcrumbs.push 'title': '资金变动历史'

    where_clause = "(
        a.amt::text like $1 or a.merno like $1 or
        a.termid like $1 or a.notes like $1
    )"
    # 如果没有提供日期，则仅查询当日
    res.locals.start_date = req.body.start_date or \
        req.query.start_date or moment().format('YYYY-MM-DD')
    res.locals.end_date = req.body.end_date or \
        req.query.end_date or moment().format('YYYY-MM-DD')

    where_clause += """
        and a.ctime::date >= '#{res.locals.start_date}'
        and a.ctime::date <= '#{res.locals.end_date}'
    """

    await pgsql.connect settings.pgsql_url, defer err, client, done
    if err then done(client); return next(err)

    await client.query \
        "select count(*)::int as count from pay_p2_fund_alter as a
            where #{where_clause} and p2_fund = $2", [
            "%#{res.locals.keyword}%", req.params.id
        ], defer err, result
    if err then done(client); return next(err)
    res.locals.total = result.rows[0].count

    await client.query \
        "select a.*,
            f.name as fund_name, f.agent as fund_agent,
            f.contact as fund_contact
            from pay_p2_fund_alter as a
            left join pay_p2_fund as f on f.uuid = a.p2_fund
            where #{where_clause} and p2_fund = $2
            order by a.serial desc offset $3::int limit $4::int", [
            "%#{res.locals.keyword}%", req.params.id
            res.locals.offset, res.locals.page_rows
        ], defer err, result
    if err then done(client); return next(err)
    done()
    res.render 'p2/fund/alter', records: result.rows
